﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Ext.Net;
using System.Data;
using System.Text;
using System.Reflection;

public partial class InsClassCount : ExtBaseRpt
{
    mrjapp mrj = new mrjapp();
    string mid;
    protected void Page_Load(object sender, EventArgs e)
    {
        mid = Request.Params["mid"];
        if (!X.IsAjaxRequest)
        {
            try
            {
                InsActLog(mid, "浏览");
                //分部
                OperationAreaName.GetStore().DataSource = mydb.DM_GetDateSet("SELECT distinct vs.OperationAreaName FROM KM_V_Ins_ShopList vs where CompanyID = 'KM' ");
                TxtStoreManager.GetStore().DataSource = mydb.DM_GetDateSet("SELECT DISTINCT tif_shopManager FROM  t_inspection_finishMaster WHERE 1=1 AND isRemove=0 AND tif_progress=1 GROUP BY tif_shopManager ");
                TxtParentName.GetStore().DataSource = mydb.DM_GetDateSet("SELECT DISTINCT ins_parentShortName,ins_parentCode From t_inspection_setting WHERE 1=1 AND isRemove = 0 ORDER BY ins_parentCode ");

                //时间默认当前月
                string DateNow = DateTime.Now.ToString("yyyy-MM-01");
                this.txtTimeBegin.Text = DateNow;
                this.txtTimeEnd.Text = Convert.ToDateTime(DateNow).AddMonths(1).AddDays(-1).ToString("yyyy-MM-dd");
            }
            catch (Exception ex)
            {
                ShowBox("错误：" + ex.Message);
            }
        }
        if (Session["UserID"] == null)
        {
            ShowBox("用户登入超时请求重新登入");
        }
    }

    //获取省份
    protected void GetBigAreaName(object sender, DirectEventArgs e)
    {
        if (string.IsNullOrWhiteSpace(OperationAreaName.Text))
        {
            txtBigAreaName.Text = string.Empty;
            txtBigAreaName.GetStore().RemoveAll();
            txtAreaName.Text = string.Empty;
            txtAreaName.GetStore().RemoveAll();
        }
        else
        {
            //先清空数据后再绑定
            txtBigAreaName.Text = string.Empty;
            txtBigAreaName.GetStore().RemoveAll();
            txtAreaName.Text = string.Empty;
            txtAreaName.GetStore().RemoveAll();

            string sql = string.Format(@"select distinct BigAreaName From V_ShopList where CompanyID='KM' and AreaID in( 
                SELECT distinct AreaID FROM KM_V_Ins_ShopList vs where CompanyID='KM' and vs.OperationAreaName='{0}')", OperationAreaName.Text);
            txtBigAreaName.GetStore().DataSource = mydb.DM_GetDateSet(sql);
            this.txtBigAreaName.DataBind();
        }
    }

    //获取城市
    protected void GetArea(object sender, DirectEventArgs e)
    {
        if (string.IsNullOrWhiteSpace(txtBigAreaName.Text))
        {
            txtAreaName.Text = string.Empty;
            txtAreaName.GetStore().RemoveAll();
        }
        else
        {
            //先清空数据后再绑定
            txtAreaName.Text = string.Empty;
            txtAreaName.GetStore().RemoveAll();

            txtAreaName.GetStore().DataSource = mydb.DM_GetDateSet("SELECT DISTINCT AreaName FROM V_ShopList WHERE BigAreaName='" + txtBigAreaName.Value + "'");
            this.Store_AreaName.DataBind();
        }
    }

    //刷新类别统计的函数
    protected void RefreshDataSet(object sender, StoreReadDataEventArgs e)
    {
        BtnQureyTypeCount(null, null);
    }

    //拼接查询和导出类别统计的条件语句
    protected string GetWhereSql()
    {
        try
        {
            if (Convert.ToDateTime(this.txtTimeBegin.Text) == Convert.ToDateTime("0001-01-01 00:00:00"))
            {
                ShowBox("请输入开始日期！");
                return "1";
            }
            if (Convert.ToDateTime(this.txtTimeEnd.Text) == Convert.ToDateTime("0001-01-01 00:00:00"))
            {
                ShowBox("请输入开始日期！");
                return "1";
            }

            string strWhere = string.Empty;
            strWhere += string.Format(@" AND fm.tif_checkDate>='{0}' ", this.txtTimeBegin.Text);
            strWhere += string.Format(@" AND fm.tif_checkDate<='{0}' ", this.txtTimeEnd.Text);
            if (!string.IsNullOrEmpty(this.TxtParentName.SelectedItem.Value))
            {
                strWhere += string.Format(@" AND ps.ins_parentCode='{0}' ", this.TxtParentName.SelectedItem.Value);
            }
            if (!string.IsNullOrEmpty(this.TxtStoreManager.Text))
            {
                strWhere += string.Format(@" AND fm.tif_shopManager='{0}' ", this.TxtStoreManager.Text);
            }
            if (!string.IsNullOrEmpty(this.txtAreaName.Text)) //城市不为空直接查询城市下的店铺
            {
                strWhere += string.Format(@" AND fm.tif_shopCode IN (SELECT ShopCode FROM V_ShopList WHERE CompanyID='KM' AND AreaName='{0}') ", this.txtAreaName.Text);
            }
            else if (!string.IsNullOrEmpty(this.txtBigAreaName.Text)) //省份不为空直接查询城市下的店铺
            {
                strWhere += string.Format(@" AND fm.tif_shopCode IN (SELECT ShopCode FROM V_ShopList WHERE CompanyID='KM' AND BigAreaName='{0}') ", this.txtBigAreaName.Text);
            }
            else if (!string.IsNullOrEmpty(this.OperationAreaName.Text)) //分部不为空直接查询城市下的店铺
            {
                strWhere += string.Format(@" AND fm.tif_shopCode IN (SELECT DISTINCT ShopCode FROM KM_V_Ins_ShopList WHERE CompanyID = 'KM' AND OperationAreaName='{0}') ", this.OperationAreaName.Text);
            }
            else
            {
                strWhere += "";
            }

            this.TxtParentName1.Text = this.TxtParentName.SelectedItem.Value;

            return strWhere;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    //获取类别统计列表数据
    protected void BtnQureyTypeCount(object sender, DirectEventArgs e)
    {
        try
        {
            string strWhere = GetWhereSql();
            if (strWhere != "1")
            {
                string strSql = string.Format(@"SELECT ROW_NUMBER() OVER (ORDER BY a.tid_points DESC) AS Num,a.* FROM 
(
	SELECT fm.tif_shopName,SUM(ps.ins_score) AS ins_score,(SUM(ps.ins_score)-SUM(fd.tid_points))+ISNULL(vb.tid_points,0) AS tid_points,(ISNULL(vd.Num,0)-ISNULL(vb.Num,0)) AS ProblemNum,fm.tif_id FROM t_inspection_finishMaster AS fm
	INNER JOIN t_inspection_finishDetail AS fd
	ON fd.tif_id=fm.tif_id AND fm.tif_progress=1 AND fm.isRemove=0 
	INNER JOIN t_inspection_setting AS ps
	ON ps.ins_id=fd.ins_id
	LEFT JOIN (
		SELECT fd.tif_id,SUM(fd.tid_points) AS tid_points,COUNT(fd.tif_id) AS Num FROM t_inspection_finishMaster AS fm
		INNER JOIN t_inspection_finishDetail AS fd
		ON fd.tif_id=fm.tif_id AND fm.tif_progress=1 AND fm.isRemove=0 
		INNER JOIN t_ins_workorderMaster AS wm
		ON fd.tid_id=wm.tid_id AND wm.w_type=3 --//只查询审核通过的
		INNER JOIN t_inspection_setting AS ps
		ON fd.ins_id=ps.ins_id
		WHERE 1=1 
        AND fm.isRemove=0 
        {0}

		--AND fm.tif_checkDate>='2018-02-22'
		--AND fm.tif_checkDate<='2018-03-11'
		--AND fm.tif_shopManager='666'
		--AND fm.tif_shopCode IN (SELECT DISTINCT ShopCode FROM KM_V_Ins_ShopList WHERE CompanyID = 'KM' AND OperationAreaName='零售1部')
		--AND fm.tif_shopCode IN (SELECT ShopCode FROM V_ShopList WHERE CompanyID='KM' AND BigAreaName='山西省')
		--AND fm.tif_shopCode IN (SELECT ShopCode FROM V_ShopList WHERE CompanyID='KM' AND AreaName='太原市')
		--AND ps.ins_parentCode='P-009'

		GROUP BY fd.tif_id
	) AS vb
	ON vb.tif_id=fd.tif_id
	LEFT JOIN (
		SELECT fm.tif_id,COUNT(fm.tif_id) AS Num FROM t_inspection_finishMaster AS fm
		INNER JOIN t_inspection_finishDetail AS fd
		ON fd.tif_id=fm.tif_id AND fm.tif_progress=1 AND fm.isRemove=0 AND fd.tid_points>0
		INNER JOIN t_inspection_setting AS ps
		ON fd.ins_id=ps.ins_id
		WHERE 1=1 
        AND fm.isRemove=0 
        {0}

		--AND fm.tif_checkDate>='2018-02-22'
		--AND fm.tif_checkDate<='2018-03-11'
		--AND fm.tif_shopManager='666'
		--AND fm.tif_shopCode IN (SELECT DISTINCT ShopCode FROM KM_V_Ins_ShopList WHERE CompanyID = 'KM' AND OperationAreaName='零售1部')
		--AND fm.tif_shopCode IN (SELECT ShopCode FROM V_ShopList WHERE CompanyID='KM' AND BigAreaName='山西省')
		--AND fm.tif_shopCode IN (SELECT ShopCode FROM V_ShopList WHERE CompanyID='KM' AND AreaName='太原市')
		--AND ps.ins_parentCode='P-009'

		GROUP BY fm.tif_id
	) AS vd
	ON vd.tif_id=fd.tif_id
	WHERE 1=1 
    AND fm.isRemove=0 
    {0}

	--AND fm.tif_checkDate>='2018-02-22'
	--AND fm.tif_checkDate<='2018-03-11'
	--AND fm.tif_shopManager='666'
	--AND fm.tif_shopCode IN (SELECT DISTINCT ShopCode FROM KM_V_Ins_ShopList WHERE CompanyID = 'KM' AND OperationAreaName='零售1部')
	--AND fm.tif_shopCode IN (SELECT ShopCode FROM V_ShopList WHERE CompanyID='KM' AND BigAreaName='山西省')
	--AND fm.tif_shopCode IN (SELECT ShopCode FROM V_ShopList WHERE CompanyID='KM' AND AreaName='太原市')
	--AND ps.ins_parentCode='P-009'

	GROUP BY fm.tif_shopName,ISNULL(vd.Num,0),ISNULL(vb.Num,0),ISNULL(vb.tid_points,0),fm.tif_id
) AS a ", strWhere);

                DataSet ds = mydb.DM_GetDateSet(strSql);
                if (ds != null)
                {
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        decimal CountScore = 0;
                        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                        {
                            CountScore += Convert.ToDecimal(ds.Tables[0].Rows[i]["tid_points"].ToString());
                        }

                        string Average = Math.Round((CountScore / ds.Tables[0].Rows.Count), 1).ToString();

                        this.Column4.Text = "得分 (" + Average + ")";
                    }
                    else
                    {
                        this.Column4.Text = "得分";
                    }

                    this.Store1.DataSource = ds;
                    this.Store1.DataBind();
                }
                else
                {
                    ShowBox("查询出错");
                }
            }
        }
        catch (Exception ex)
        {
            ShowBox("查询出错：" + ex.Message);
        }
    }

    //导出类别统计Excel
    protected void BtnTypeCountOutExcel(object sender, DirectEventArgs e)
    {
        //导出EXCEL
        //string str = StrSplit(this.selectshop.Text);

        try
        {
            string strWhere = GetWhereSql();
            if (strWhere != "1")
            {
                string strSql = string.Format(@"SELECT a.Num AS 排名,a.tif_shopName AS 店铺,a.ins_score AS 总分,a.tid_points AS 得分,a.ProblemNum AS 问题数,ISNULL(b.ins_checkContext,'') AS 问题详情,ISNULL(b.ins_score,0) AS 扣分数 FROM 
(
	SELECT ROW_NUMBER() OVER (ORDER BY a.tid_points DESC) AS Num,a.* FROM 
	(
		SELECT fm.tif_shopName,SUM(ps.ins_score) AS ins_score,(SUM(ps.ins_score)-SUM(fd.tid_points))+ISNULL(vb.tid_points,0) AS tid_points,(ISNULL(vd.Num,0)-ISNULL(vb.Num,0)) AS ProblemNum,fm.tif_id FROM t_inspection_finishMaster AS fm
		INNER JOIN t_inspection_finishDetail AS fd
		ON fd.tif_id=fm.tif_id AND fm.tif_progress=1 AND fm.isRemove=0 
		INNER JOIN t_inspection_setting AS ps
		ON ps.ins_id=fd.ins_id
		LEFT JOIN (
			SELECT fd.tif_id,SUM(fd.tid_points) AS tid_points,COUNT(fd.tif_id) AS Num FROM t_inspection_finishMaster AS fm
			INNER JOIN t_inspection_finishDetail AS fd
			ON fd.tif_id=fm.tif_id AND fm.tif_progress=1 AND fm.isRemove=0 
			INNER JOIN t_ins_workorderMaster AS wm
			ON fd.tid_id=wm.tid_id AND wm.w_type=3 --//只查询审核通过的
			INNER JOIN t_inspection_setting AS ps
			ON fd.ins_id=ps.ins_id
			WHERE 1=1 
            AND fm.isRemove=0 
            {0}

			--AND fm.tif_checkDate>='2018-02-22'
			--AND fm.tif_checkDate<='2018-03-11'
			--AND fm.tif_shopManager='666'
			--AND fm.tif_shopCode IN (SELECT DISTINCT ShopCode FROM KM_V_Ins_ShopList WHERE CompanyID = 'KM' AND OperationAreaName='零售1部')
			--AND fm.tif_shopCode IN (SELECT ShopCode FROM V_ShopList WHERE CompanyID='KM' AND BigAreaName='山西省')
			--AND fm.tif_shopCode IN (SELECT ShopCode FROM V_ShopList WHERE CompanyID='KM' AND AreaName='太原市')
			--AND ps.ins_parentCode='P-009'

			GROUP BY fd.tif_id
		) AS vb
		ON vb.tif_id=fd.tif_id
		LEFT JOIN (
			SELECT fm.tif_id,COUNT(fm.tif_id) AS Num FROM t_inspection_finishMaster AS fm
			INNER JOIN t_inspection_finishDetail AS fd
			ON fd.tif_id=fm.tif_id AND fm.tif_progress=1 AND fm.isRemove=0 AND fd.tid_points>0
			INNER JOIN t_inspection_setting AS ps
			ON fd.ins_id=ps.ins_id
			WHERE 1=1 
            AND fm.isRemove=0 
            {0}

			--AND fm.tif_checkDate>='2018-02-22'
			--AND fm.tif_checkDate<='2018-03-11'
			--AND fm.tif_shopManager='666'
			--AND fm.tif_shopCode IN (SELECT DISTINCT ShopCode FROM KM_V_Ins_ShopList WHERE CompanyID = 'KM' AND OperationAreaName='零售1部')
			--AND fm.tif_shopCode IN (SELECT ShopCode FROM V_ShopList WHERE CompanyID='KM' AND BigAreaName='山西省')
			--AND fm.tif_shopCode IN (SELECT ShopCode FROM V_ShopList WHERE CompanyID='KM' AND AreaName='太原市')
			--AND ps.ins_parentCode='P-009'

			GROUP BY fm.tif_id
		) AS vd
		ON vd.tif_id=fd.tif_id
		WHERE 1=1 
        AND fm.isRemove=0
        {0}

		--AND fm.tif_checkDate>='2018-02-22'
		--AND fm.tif_checkDate<='2018-03-11'
		--AND fm.tif_shopManager='666'
		--AND fm.tif_shopCode IN (SELECT DISTINCT ShopCode FROM KM_V_Ins_ShopList WHERE CompanyID = 'KM' AND OperationAreaName='零售1部')
		--AND fm.tif_shopCode IN (SELECT ShopCode FROM V_ShopList WHERE CompanyID='KM' AND BigAreaName='山西省')
		--AND fm.tif_shopCode IN (SELECT ShopCode FROM V_ShopList WHERE CompanyID='KM' AND AreaName='太原市')
		--AND ps.ins_parentCode='P-009'

		GROUP BY fm.tif_shopName,ISNULL(vd.Num,0),ISNULL(vb.Num,0),ISNULL(vb.tid_points,0),fm.tif_id 
	) AS a
) AS a
LEFT JOIN 
(
	SELECT fd.tif_id,ps.ins_checkContext,ps.ins_score FROM t_inspection_finishMaster AS fm
	INNER JOIN t_inspection_finishDetail AS fd
	ON fd.tif_id=fm.tif_id AND fm.tif_progress=1 AND fm.isRemove=0 AND fd.tid_points>0
	INNER JOIN t_inspection_setting AS ps
	ON fd.ins_id=ps.ins_id 
	INNER JOIN 
	(
		SELECT fd.tid_id FROM t_inspection_finishDetail AS fd
　　	  EXCEPT
　　	  SELECT wm.tid_id FROM t_ins_workorderMaster AS wm
		WHERE 1=1 
		AND wm.w_type=3
	) AS a
	ON fd.tid_id=a.tid_id
    WHERE 1=1 
    AND fm.isRemove=0 
) AS b
ON a.tif_id=b.tif_id
ORDER BY a.Num ", strWhere);

                DataSet ds = mydb.DM_GetDateSet(strSql);
                string path = NpoiHelper.ExportByWeb(mid, ds, this.Title + "类别");
                this.ResourceManager1.AddScript("outExcle('" + path + "')");
            }
        }
        catch (Exception ex)
        {
            Activity.Log.Error("请求报表数据异常：" + ex);
            ShowBox("导出出错：" + ex.Message);
        }
    }

    //查看问题详情
    [DirectMethod(Namespace = "CompanyX", Timeout = 180000)]
    public void GetProblemDetails(string JsonParams)
    {
        try
        {
            string strWhere = string.Empty;
            if (!string.IsNullOrEmpty(this.TxtParentName1.Text))
            {
                strWhere += string.Format(@" AND ps.ins_parentCode='{0}' ", this.TxtParentName1.Text);
            }

            string strSql = string.Format(@"SELECT ROW_NUMBER() OVER (ORDER BY fd.tid_id) AS Num,ps.ins_checkContext,ps.ins_score FROM t_inspection_finishMaster AS fm
INNER JOIN t_inspection_finishDetail AS fd
ON fd.tif_id=fm.tif_id AND fm.tif_progress=1 AND fm.isRemove=0 AND fd.tid_points>0
INNER JOIN t_inspection_setting AS ps
ON fd.ins_id=ps.ins_id 
INNER JOIN 
(
	SELECT fd.tid_id FROM t_inspection_finishDetail AS fd
	WHERE 1=1 
	AND fd.tif_id='{0}'
　　EXCEPT
　　SELECT wm.tid_id FROM t_ins_workorderMaster AS wm
	WHERE 1=1 
	AND wm.w_type=3
) AS a
ON fd.tid_id=a.tid_id
WHERE 1=1 
AND fm.isRemove=0 
AND fm.tif_id='{0}' 
{1} ", JsonParams, strWhere);

            this.ProblemList.DataSource = mydb.DM_GetDateSet(strSql);
            this.ProblemList.DataBind();
        }
        catch (Exception ex)
        {
            ShowBox("查询出错：" + ex.Message);
        }
    }




    //刷新小类统计的函数
    protected void RefreshMinDataSet(object sender, StoreReadDataEventArgs e)
    {
        BtnQureyMinTypeCount(null, null);
    }

    //小类统计查询条件
    protected string GetWhereSql1()
    {
        try
        {
            if (Convert.ToDateTime(this.txtTimeBegin.Text) == Convert.ToDateTime("0001-01-01 00:00:00"))
            {
                ShowBox("请输入开始日期！");
                return "1";
            }
            if (Convert.ToDateTime(this.txtTimeEnd.Text) == Convert.ToDateTime("0001-01-01 00:00:00"))
            {
                ShowBox("请输入开始日期！");
                return "1";
            }

            string strWhere = string.Empty;
            strWhere += string.Format(@" AND fm.tif_checkDate>='{0}' ", this.txtTimeBegin.Text);
            strWhere += string.Format(@" AND fm.tif_checkDate<='{0}' ", this.txtTimeEnd.Text);

            if (!string.IsNullOrEmpty(this.txtAreaName.Text)) //城市不为空直接查询城市下的店铺
            {
                strWhere += string.Format(@" AND fm.tif_shopCode IN (SELECT ShopCode FROM V_ShopList WHERE CompanyID='KM' AND AreaName='{0}') ", this.txtAreaName.Text);
            }
            else if (!string.IsNullOrEmpty(this.txtBigAreaName.Text)) //省份不为空直接查询城市下的店铺
            {
                strWhere += string.Format(@" AND fm.tif_shopCode IN (SELECT ShopCode FROM V_ShopList WHERE CompanyID='KM' AND BigAreaName='{0}') ", this.txtBigAreaName.Text);
            }
            else if (!string.IsNullOrEmpty(this.OperationAreaName.Text)) //分部不为空直接查询城市下的店铺
            {
                strWhere += string.Format(@" AND fm.tif_shopCode IN (SELECT DISTINCT ShopCode FROM KM_V_Ins_ShopList WHERE CompanyID = 'KM' AND OperationAreaName='{0}') ", this.OperationAreaName.Text);
            }
            else
            {
                strWhere += "";
            }

            //保存查询条件到隐藏控件
            this.txtAreaName1.Text = this.txtAreaName.Text;
            this.txtBigAreaName2.Text = this.txtBigAreaName.Text;
            this.OperationAreaName3.Text = this.OperationAreaName.Text;
            this.txtTimeBegin4.Text = this.txtTimeBegin.Text;
            this.txtTimeEnd5.Text = this.txtTimeEnd.Text;

            return strWhere;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    //获取小类统计列表数据
    protected void BtnQureyMinTypeCount(object sender, DirectEventArgs e)
    {
        try
        {
            string strWhere = GetWhereSql1();
            if (strWhere != "1")
            {

                string strSql = string.Format(@"SELECT ROW_NUMBER() OVER (ORDER BY a.ProportionNum DESC) AS Num,a.* FROM
(
SELECT 
	ps.ins_parentShortName,
	ps.ins_checkContext,
	fd.ins_id,
	COUNT(fd.tif_id) AS ProblemShopNum,
	CONVERT(VARCHAR(10),CONVERT(decimal(10,1),(CAST(COUNT(fd.tif_id) as decimal(10,1))/CAST(b.CountShopNum as decimal(10,1)))*100))+'%' AS ProportionNum 
FROM t_inspection_finishMaster AS fm
INNER JOIN t_inspection_finishDetail AS fd
ON fd.tif_id=fm.tif_id AND fm.tif_progress=1 AND fm.isRemove=0 AND fd.tid_points>0
INNER JOIN t_inspection_setting AS ps
ON fd.ins_id=ps.ins_id 
INNER JOIN 
(
	SELECT fd.tid_id FROM t_inspection_finishDetail AS fd
　　EXCEPT 
　　SELECT wm.tid_id FROM t_ins_workorderMaster AS wm
	WHERE 1=1 
	AND wm.w_type=3
) AS a
ON fd.tid_id=a.tid_id
INNER JOIN 
(
	SELECT fd.ins_id,COUNT(fd.tif_id) AS CountShopNum FROM t_inspection_finishMaster AS fm
	INNER JOIN t_inspection_finishDetail AS fd
	ON fd.tif_id=fm.tif_id AND fm.tif_progress=1 AND fm.isRemove=0 
	INNER JOIN t_inspection_setting AS ps
	ON fd.ins_id=ps.ins_id 
	WHERE 1=1 
    AND fm.isRemove=0 
    {0}

	--AND fm.tif_checkDate>='2018-03-01'
	--AND fm.tif_checkDate<='2018-03-31'
	--AND fm.tif_shopCode IN (SELECT DISTINCT ShopCode FROM KM_V_Ins_ShopList WHERE CompanyID = 'KM' AND OperationAreaName='零售9部')
	--AND fm.tif_shopCode IN (SELECT ShopCode FROM V_ShopList WHERE CompanyID='KM' AND BigAreaName='山西省')
	--AND fm.tif_shopCode IN (SELECT ShopCode FROM V_ShopList WHERE CompanyID='KM' AND AreaName='太原市')

	GROUP BY fd.ins_id
) AS b
ON b.ins_id=fd.ins_id
WHERE 1=1 
AND fm.isRemove=0 
{0}

--AND fm.tif_checkDate>='2018-03-01'
--AND fm.tif_checkDate<='2018-03-31'
--AND fm.tif_shopCode IN (SELECT DISTINCT ShopCode FROM KM_V_Ins_ShopList WHERE CompanyID = 'KM' AND OperationAreaName='零售9部')
--AND fm.tif_shopCode IN (SELECT ShopCode FROM V_ShopList WHERE CompanyID='KM' AND BigAreaName='山西省')
--AND fm.tif_shopCode IN (SELECT ShopCode FROM V_ShopList WHERE CompanyID='KM' AND AreaName='太原市')

GROUP BY ps.ins_parentShortName,ps.ins_checkContext,fd.ins_id,b.CountShopNum
) AS a ", strWhere);

                this.Store3.DataSource = mydb.DM_GetDateSet(strSql);
                this.Store3.DataBind();
            }
        }
        catch (Exception ex)
        {
            ShowBox("查询出错：" + ex.Message);
        }
    }

    //导出小类统计Excel
    protected void BtnMinTypeCountOutExcel(object sender, DirectEventArgs e)
    {
        //导出EXCEL
        //string str = StrSplit(this.selectshop.Text);

        try
        {
            string strWhere = GetWhereSql1();
            if (strWhere != "1")
            {
                string strSql = string.Format(@"SELECT a.Num AS 排名,a.ins_parentShortName AS 类别,a.ins_checkContext AS 小类,a.ProblemShopNum AS 问题店铺总计,a.ProportionNum AS 占比,b.tif_shopName AS 扣分店铺 FROM 
(
	SELECT ROW_NUMBER() OVER (ORDER BY a.ProportionNum DESC) AS Num,a.* FROM
	(
		SELECT 
			ps.ins_parentShortName,
			ps.ins_checkContext,
			fd.ins_id,
			COUNT(fd.tif_id) AS ProblemShopNum,
			CONVERT(VARCHAR(10),CONVERT(decimal(10,1),(CAST(COUNT(fd.tif_id) as decimal(10,1))/CAST(b.CountShopNum as decimal(10,1)))*100))+'%' AS ProportionNum 
		FROM t_inspection_finishMaster AS fm
		INNER JOIN t_inspection_finishDetail AS fd
		ON fd.tif_id=fm.tif_id AND fm.tif_progress=1 AND fm.isRemove=0 AND fd.tid_points>0
		INNER JOIN t_inspection_setting AS ps
		ON fd.ins_id=ps.ins_id 
		INNER JOIN 
		(
			SELECT fd.tid_id FROM t_inspection_finishDetail AS fd
　　		  EXCEPT 
　　		  SELECT wm.tid_id FROM t_ins_workorderMaster AS wm
			WHERE 1=1 
			AND wm.w_type=3
		) AS a
		ON fd.tid_id=a.tid_id
		INNER JOIN 
		(
			SELECT fd.ins_id,COUNT(fd.tif_id) AS CountShopNum FROM t_inspection_finishMaster AS fm
			INNER JOIN t_inspection_finishDetail AS fd
			ON fd.tif_id=fm.tif_id AND fm.tif_progress=1 AND fm.isRemove=0 
			INNER JOIN t_inspection_setting AS ps
			ON fd.ins_id=ps.ins_id 
			WHERE 1=1 
            AND fm.isRemove=0 
            {0}

			--AND fm.tif_checkDate>='2018-03-01'
			--AND fm.tif_checkDate<='2018-03-31'
			--AND fm.tif_shopCode IN (SELECT DISTINCT ShopCode FROM KM_V_Ins_ShopList WHERE CompanyID = 'KM' AND OperationAreaName='零售9部')
			--AND fm.tif_shopCode IN (SELECT ShopCode FROM V_ShopList WHERE CompanyID='KM' AND BigAreaName='山西省')
			--AND fm.tif_shopCode IN (SELECT ShopCode FROM V_ShopList WHERE CompanyID='KM' AND AreaName='太原市')

			GROUP BY fd.ins_id
		) AS b
		ON b.ins_id=fd.ins_id
		WHERE 1=1 
        AND fm.isRemove=0 
        {0}

		--AND fm.tif_checkDate>='2018-03-01'
		--AND fm.tif_checkDate<='2018-03-31'
		--AND fm.tif_shopCode IN (SELECT DISTINCT ShopCode FROM KM_V_Ins_ShopList WHERE CompanyID = 'KM' AND OperationAreaName='零售9部')
		--AND fm.tif_shopCode IN (SELECT ShopCode FROM V_ShopList WHERE CompanyID='KM' AND BigAreaName='山西省')
		--AND fm.tif_shopCode IN (SELECT ShopCode FROM V_ShopList WHERE CompanyID='KM' AND AreaName='太原市')

		GROUP BY ps.ins_parentShortName,ps.ins_checkContext,fd.ins_id,b.CountShopNum
	) AS a
) AS a
INNER JOIN
(
SELECT fm.tif_shopName,fd.ins_id FROM t_inspection_finishMaster AS fm
INNER JOIN t_inspection_finishDetail AS fd
ON fd.tif_id=fm.tif_id AND fm.tif_progress=1 AND fm.isRemove=0 AND fd.tid_points>0
INNER JOIN 
(
	SELECT fd.tid_id FROM t_inspection_finishDetail AS fd
　　EXCEPT 
　　SELECT wm.tid_id FROM t_ins_workorderMaster AS wm
	WHERE 1=1 
	AND wm.w_type=3
) AS a
ON fd.tid_id=a.tid_id
WHERE 1=1 
AND fm.isRemove=0 
{0}

--AND fm.tif_checkDate>='2018-03-01'
--AND fm.tif_checkDate<='2018-03-31'
--AND fm.tif_shopCode IN (SELECT DISTINCT ShopCode FROM KM_V_Ins_ShopList WHERE CompanyID = 'KM' AND OperationAreaName='零售9部')
--AND fm.tif_shopCode IN (SELECT ShopCode FROM V_ShopList WHERE CompanyID='KM' AND BigAreaName='山西省')
--AND fm.tif_shopCode IN (SELECT ShopCode FROM V_ShopList WHERE CompanyID='KM' AND AreaName='太原市')
) AS b
ON a.ins_id=b.ins_id
ORDER BY a.Num ", strWhere);

                DataSet ds = mydb.DM_GetDateSet(strSql);
                string path = NpoiHelper.ExportByWeb(mid, ds, this.Title + "小类");
                this.ResourceManager1.AddScript("outExcle('" + path + "')");
            }
        }
        catch (Exception ex)
        {
            Activity.Log.Error("请求报表数据异常：" + ex);
            ShowBox("导出出错：" + ex.Message);
        }
    }

    //问题店铺查询条件 
    protected string GetWhereSql2()
    {
        try
        {
            if (Convert.ToDateTime(this.txtTimeBegin4.Text) == Convert.ToDateTime("0001-01-01 00:00:00"))
            {
                ShowBox("请输入开始日期！");
                return "1";
            }
            if (Convert.ToDateTime(this.txtTimeEnd5.Text) == Convert.ToDateTime("0001-01-01 00:00:00"))
            {
                ShowBox("请输入开始日期！");
                return "1";
            }

            string strWhere = string.Empty;
            strWhere += string.Format(@" AND fm.tif_checkDate>='{0}' ", this.txtTimeBegin4.Text);
            strWhere += string.Format(@" AND fm.tif_checkDate<='{0}' ", this.txtTimeEnd5.Text);

            if (!string.IsNullOrEmpty(this.txtAreaName1.Text)) //城市不为空直接查询城市下的店铺
            {
                strWhere += string.Format(@" AND fm.tif_shopCode IN (SELECT ShopCode FROM V_ShopList WHERE CompanyID='KM' AND AreaName='{0}') ", this.txtAreaName1.Text);
            }
            else if (!string.IsNullOrEmpty(this.txtBigAreaName2.Text)) //省份不为空直接查询城市下的店铺
            {
                strWhere += string.Format(@" AND fm.tif_shopCode IN (SELECT ShopCode FROM V_ShopList WHERE CompanyID='KM' AND BigAreaName='{0}') ", this.txtBigAreaName2.Text);
            }
            else if (!string.IsNullOrEmpty(this.OperationAreaName3.Text)) //分部不为空直接查询城市下的店铺
            {
                strWhere += string.Format(@" AND fm.tif_shopCode IN (SELECT DISTINCT ShopCode FROM KM_V_Ins_ShopList WHERE CompanyID = 'KM' AND OperationAreaName='{0}') ", this.OperationAreaName3.Text);
            }
            else
            {
                strWhere += "";
            }

            return strWhere;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    //查看问题店铺详情
    [DirectMethod(Namespace = "CompanyX", Timeout = 180000)]
    public void GetProblemShopDetail(string JsonParams)
    {
        try
        {
            string strWhere = GetWhereSql2();
            if (strWhere != "1")
            {

                string strSql = string.Format(@"SELECT ROW_NUMBER() OVER (ORDER BY fm.tif_shopName) AS Num,fm.tif_shopName FROM t_inspection_finishMaster AS fm
INNER JOIN t_inspection_finishDetail AS fd
ON fd.tif_id=fm.tif_id AND fm.tif_progress=1 AND fm.isRemove=0 AND fd.tid_points>0
INNER JOIN 
(
	SELECT fd.tid_id FROM t_inspection_finishDetail AS fd
　　EXCEPT 
　　SELECT wm.tid_id FROM t_ins_workorderMaster AS wm
	WHERE 1=1 
	AND wm.w_type=3
) AS a
ON fd.tid_id=a.tid_id
WHERE 1=1 
AND fm.isRemove=0 
AND fd.ins_id='{0}'
{1}

--AND fm.tif_checkDate>='2018-03-01'
--AND fm.tif_checkDate<='2018-03-31'
--AND fm.tif_shopCode IN (SELECT DISTINCT ShopCode FROM KM_V_Ins_ShopList WHERE CompanyID = 'KM' AND OperationAreaName='零售9部')
--AND fm.tif_shopCode IN (SELECT ShopCode FROM V_ShopList WHERE CompanyID='KM' AND BigAreaName='山西省')
--AND fm.tif_shopCode IN (SELECT ShopCode FROM V_ShopList WHERE CompanyID='KM' AND AreaName='太原市') ", JsonParams, strWhere);

                this.Store4.DataSource = mydb.DM_GetDateSet(strSql);
                this.Store4.DataBind();
            }
        }
        catch (Exception ex)
        {
            ShowBox("查询出错：" + ex.Message);
        }
    }
}